﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlServerCe;
using System.Diagnostics;
using System.Linq;

namespace Flare.DB
{
    class endpoints
    {
        public IEnumerable<Entity.EndpointView> Index(string search_string = null, int? id_department = null, int? id_room = null, int? id_type = null)
        {
            var result = from e in Source.endpoints()
                         join r in Source.general_room() on e.id_room equals r.id_room
                         join d in Source.general_department() on r.id_department equals d.id_department

                         where (search_string == null ||
                         e.endpoint.IndexOf(search_string, StringComparison.CurrentCultureIgnoreCase) != -1 ||
                         e.phone.IndexOf(search_string, StringComparison.CurrentCultureIgnoreCase) != -1 ||
                         e.net_ip.IndexOf(search_string, StringComparison.CurrentCultureIgnoreCase) != -1) &&
                         (id_department == null || d.id_department == id_department) &&
                         (id_room == null || r.id_room == id_room) &&
                         (id_type == null || e.id_type == id_type)

                         select new Entity.EndpointView
                         {
                             id = e.id_endpoint,
                             id_type = e.id_type.Value,
                             ip = e.net_ip,
                             name = e.endpoint,
                             phone = e.phone,
                             room = (r.room == d.department) ? r.room : string.Format("{0}/{1}", d.department, r.room)
                         };
            return result.Take(40);
        }

        public void Create(Entity.Endpoint data)
        {
            var validate = data.Validate();
            if (validate.Count() != 0)
            {
                throw new Exception(string.Join<string>("\n", validate));
            }
            using (SqlCeConnection connection = new SqlCeConnection(glob.DBConnection))
            {
                using (SqlCeCommand command = new SqlCeCommand(@"INSERT INTO [endpoints] ([endpoint],[id_type],[id_room],[net_ip],[phone]) 
                                                             VALUES (@name,@id_type,@id_room,@net_ip,@phone)", connection))
                {
                    command.Parameters.Add("@name", SqlDbType.NVarChar).Value = data.name;
                    command.Parameters.Add("@id_type", SqlDbType.Int).Value = data.id_type;
                    command.Parameters.Add("@id_room", SqlDbType.Int).Value = data.id_room;
                    command.Parameters.Add("@net_ip", SqlDbType.NVarChar).Value = data.net_ip;
                    command.Parameters.Add("@phone", SqlDbType.NVarChar).Value = (object)data.phone ?? DBNull.Value;

                    try
                    {
                        connection.Open();
                    }
                    catch (Exception ex)
                    {
                        glob.kernel.Panic(glob.PanicType.db_connect, ex);
                    }

                    try
                    {
                        command.ExecuteNonQuery();
                    }
                    catch (Exception ex)
                    {
                        Debug.WriteLine(command.CommandText + "\n" + ex.Message);
                        throw new Exception("Во время добавления данных произошла ошибка. Данные не сохранены");
                    }
                }
            }
        }

        public void Edit(Entity.Endpoint data)
        {
            var validate = data.Validate();
            if (validate.Count() != 0)
            {
                throw new Exception(string.Join<string>("\n", validate));
            }

            using (SqlCeConnection connection = new SqlCeConnection(glob.DBConnection))
            {
                using (SqlCeCommand command = new SqlCeCommand(@"UPDATE [endpoints] SET [endpoint]=@name,[id_type]=@id_type,[id_room]=@id_room,[net_ip]=@net_ip,
                                                            [phone]=@phone WHERE [id_endpoint]=@id_endpoint", connection))
                {
                    command.Parameters.Add("@id_endpoint", SqlDbType.Int).Value = data.id;
                    command.Parameters.Add("@name", SqlDbType.NVarChar).Value = data.name;
                    command.Parameters.Add("@id_type", SqlDbType.Int).Value = data.id_type;
                    command.Parameters.Add("@id_room", SqlDbType.Int).Value = data.id_room;
                    command.Parameters.Add("@net_ip", SqlDbType.NVarChar).Value = data.net_ip;
                    command.Parameters.Add("@phone", SqlDbType.NVarChar).Value = (object)data.phone ?? DBNull.Value;
                    try
                    {
                        connection.Open();
                    }
                    catch (Exception ex)
                    {
                        glob.kernel.Panic(glob.PanicType.db_connect, ex);
                    }

                    try
                    {
                        command.ExecuteNonQuery();
                    }
                    catch (Exception ex)
                    {
                        Debug.WriteLine(command.CommandText + "\n" + ex.Message);
                        throw new Exception("Во время обновления данных произошла ошибка. Данные не сохранены");
                    }
                }
            }
        }

        public void Delete(int id)
        {
            using (SqlCeConnection connection = new SqlCeConnection(glob.DBConnection))
            {
                using (SqlCeCommand command = new SqlCeCommand(@"DELETE FROM [endpoints] WHERE [id_endpoint]=@id_endpoint", connection))
                {
                    command.Parameters.Add("@id_endpoint", SqlDbType.Int).Value = id;
                    try
                    {
                        connection.Open();
                    }
                    catch (Exception ex)
                    {
                        glob.kernel.Panic(glob.PanicType.db_connect, ex);
                    }
                    try
                    {
                        command.ExecuteNonQuery();
                    }
                    catch (Exception ex)
                    {
                        Debug.WriteLine(command.CommandText + "\n" + ex.Message);
                        throw new Exception("Во время удаления данных произошла ошибка. Данные не сохранены");
                    }
                }
            }
        }

        public Entity.EndpointDetails Details(int id_endpoint)
        {
            var result = from e in Source.endpoints()
                         join t in Source.endpoint_types() on e.id_type equals t.id_type
                         join r in Source.general_room() on e.id_room equals r.id_room
                         join d in Source.general_department() on r.id_department equals d.id_department

                         where e.id_endpoint == id_endpoint

                         select new Entity.EndpointDetails
                         {
                             id = e.id_endpoint,
                             id_type = e.id_type.Value,
                             type = t.type,
                             ip = e.net_ip,
                             name = e.endpoint,
                             phone = e.phone,
                             id_department = d.id_department,
                             department = d.department,
                             id_room = r.id_room,
                             room = r.room
                         };
            return result.First();
        }
    }
}
